﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
//using Oracle.DataAccess.Client;
using System.Configuration;
//using System.Data.Entity.Infrastructure;
using System.ComponentModel.DataAnnotations;
using DslamPort.Models;
using System.Collections.ObjectModel;
using System.Web.Mvc;
using System.Data.OracleClient;
using System.Data;

namespace DslamPort.Models
{
    public class UserModel
    {
        public int id { get; set; }
        [Required(ErrorMessage = "Tên đăng nhập không được để trống!")]
        public string user_name { get; set; }
        [Required(ErrorMessage = "Mật khẩu không được để trống!")]
        public string password { get; set; }
        public int quan_huyen_id { get; set; }
        public SelectList donvi { get; set; }
        public string status { get; set; }
        public string ho_ten { get; set; }
        public string ten_qh { get; set; } 
        public bool RememberMe { get; set; }

        public List<UserModel> getUser()
        {
            List<UserModel> user_ = new List<UserModel>();
            var odr = BaseClass.Lib_GetDataReader("select a.*,b.ten  from nguoi_dung a,quan_huyen b where a.quan_huyen_id=b.quan_huyen_id");
            foreach (DataRow item in odr.Rows)
            {
                user_.Add(new UserModel
                {
                    id = Convert.ToInt32(item["id"].ToString()),
                    user_name = item["user_name"].ToString(),
                    password = item["password"].ToString(),
                    quan_huyen_id = Convert.ToInt32(item["quan_huyen_id"].ToString()),
                    status = item["status"].ToString(),
                    ho_ten = item["ho_ten"].ToString(),
                    ten_qh = item["ten"].ToString()

                });

            }
            
            return user_;
        }
        public List<UserModel> getUserByName(string name)
        {
            List<UserModel> user_ = new List<UserModel>();
            var odr = BaseClass.Lib_GetDataReader("select a.*,b.ten ten from nguoi_dung a,quan_huyen b where a.quan_huyen_id=b.quan_huyen_id and user_name='" + name + "'");
            foreach (DataRow item in odr.Rows)
            {
                user_.Add(new UserModel
                {
                    id = Convert.ToInt32(item["id"].ToString()),
                    user_name = item["user_name"].ToString(),
                    password = item["password"].ToString(),
                    quan_huyen_id = Convert.ToInt32(item["quan_huyen_id"].ToString()),
                    status = item["status"].ToString(),
                    ho_ten = item["ho_ten"].ToString(),
                    ten_qh = item["ten"].ToString()
                });

            }
            
            return user_;
        }
        public static int IsValid(string username, string password)
        {
            string sql = "SELECT * FROM nguoi_dung WHERE user_name='" + username + "' and password='" + password + "' and status>0";
            return BaseClass.Lib_CheckExist(sql);
        }
        public void addUser(UserModel us)
        {
            string sql = "INSERT INTO nguoi_dung";
            sql += " select nvl((select max(id)+1 from nguoi_dung),1),'" + us.user_name + "','" + us.password + "'," + us.status + "," + us.quan_huyen_id + ",'" + us.ho_ten + "' from dual";
            BaseClass.Lib_ExecuteNonQuery(sql);
        }

        public void updateUser(UserModel us)
        {
            string sql = "UPDATE nguoi_dung SET  user_name='" + us.user_name + "', password='" + us.password + "',  status=" + us.status + ",quan_huyen_id="+us.quan_huyen_id+", ho_ten='" + us.ho_ten + "' where id='" + us.id + "'";
            BaseClass.Lib_ExecuteNonQuery(sql);

            //string sql2 = DatabaseCore.str_database;
            //string a = "";
            //string b = "";
            //foreach (var prop in us.GetType().GetProperties())
            //{
            //    a = prop.Name + ",";
            //    b = prop.GetValue(us, null).ToString();
            //    Console.WriteLine("{0}={1}", prop.Name, prop.GetValue(us, null));
            //}
            //string c = "";
        }
        public void updateCurrentUser(UserModel us)
        {
            string sql = "UPDATE nguoi_dung SET user_name='" + us.user_name + "', password='" + us.password + "', ho_ten='" + us.ho_ten + "' where id='" + us.id + "'";
            BaseClass.Lib_ExecuteNonQuery(sql);
        }
        public void deleteUser(int id)
        {
            string sql = "DELETE FROM nguoi_dung WHERE id=" + id + "";
            BaseClass.Lib_ExecuteNonQuery(sql);
        }
        public int checkExist(UserModel us)
        {
            string sql = "SELECT * FROM nguoi_dung WHERE user_name='" + us.user_name + "'";
            return BaseClass.Lib_CheckExist(sql);
        }
        public List<UserModel> getDonvi()
        {
            List<UserModel> user_ = new List<UserModel>();
            var odr = BaseClass.Lib_GetDataReader("select quan_huyen_id,ten from quan_huyen");
            
            foreach (DataRow item in odr.Rows)
            {
                user_.Add(new UserModel
                {
                    quan_huyen_id = Convert.ToInt32(item["quan_huyen_id"].ToString()),
                    ten_qh = item["ten"].ToString()
                });

            }
            return user_;
        }

    }
    public class UserAssociateModel
    {
        public int id { get; set; }
        public string user_name { get; set; }
        public string password { get; set; }
        public string stations_name { get; set; }
        public string main_stations_name { get; set; }
        public string quyen { get; set; }
        public string status { get; set; }
        public string ho_ten { get; set; }
        public string so_dt { get; set; }
        public string email { get; set; }
        public string ghi_chu { get; set; }
        public bool RememberMe { get; set; }
        public List<UserAssociateModel> getUser()
        {
            List<UserAssociateModel> user_ = new List<UserAssociateModel>();
            var odr = BaseClass.Lib_GetDataReader("SELECT a.id,a.user_name,a.password,b.name,c.name,d.name,a.status,a.ho_ten,a.so_dt,a.email,a.ghi_chu FROM nguoi_dung a,stations b,main_stations c,permission d where a.stations_id=b.id and a.main_stations_id=c.id and a.quyen=d.id order by a.id");
            foreach (DataRow item in odr.Rows)
            {
                user_.Add(new UserAssociateModel { id = Convert.ToInt32(item[0].ToString()), user_name = item[1].ToString(), password = item[2].ToString(), stations_name = item[3].ToString(), main_stations_name = item[4].ToString(), quyen = item[5].ToString(), status = Convert.ToInt32(item[6].ToString()) == 1 ? "Bình thường" : "Khóa", ho_ten = item[7].ToString(), so_dt = item[8].ToString(), email = item[9].ToString(), ghi_chu = item[10].ToString() });

            }
            
            return user_;
        }
        public List<UserAssociateModel> getUserByName(string name)
        {
            List<UserAssociateModel> user_ = new List<UserAssociateModel>();
            var odr = BaseClass.Lib_GetDataReader("SELECT a.id,a.user_name,a.password,b.name,c.name,d.name,a.status,a.ho_ten,a.so_dt,a.email,a.ghi_chu FROM nguoi_dung a,stations b,main_stations c,permission d where a.stations_id=b.id and a.main_stations_id=c.id and a.quyen=d.id and a.user_name='" + name + "' order by a.id");
            foreach (DataRow item in odr.Rows)
            {
                user_.Add(new UserAssociateModel { id = Convert.ToInt32(item[0].ToString()), user_name = item[1].ToString(), password = item[2].ToString(), stations_name = item[3].ToString(), main_stations_name = item[4].ToString(), quyen = item[5].ToString(), status = Convert.ToInt32(item[6].ToString()) == 1 ? "Bình thường" : "Khóa", ho_ten = item[7].ToString(), so_dt = item[8].ToString(), email = item[9].ToString(), ghi_chu = item[10].ToString() });

            }
            return user_;
        }
    }

}
